package com.dctp.ems.controller;

import com.dctp.common.cache.ContractCache;
import com.dctp.common.controller.BaseController;
import com.dctp.common.utils.CollectUtil;
import com.dctp.common.utils.DateUtil;
import com.dctp.common.cache.UserCache;
import com.dctp.common.utils.StringUtil;
import com.dctp.common.vo.JsonResult;
import com.dctp.common.vo.PageResult;
import com.dctp.common.vo.QueryResult;
import com.jfinal.core.Path;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.List;
import java.time.LocalDateTime;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletResponse;

@Path("/fundFlow")
public class FundFlowController extends BaseController {

    /**
     * GET /fundFlow/list
     * 分页查询资金流水，支持user_id、change_type、create_time范围
     */
    public void list() {
        int page = getInt("currentPage", 1);
        int pageSize = getInt("pageSize", 10);
        QueryResult qr = buildFundFlowQuery();
        String select = "SELECT * ";
        String from = "FROM dctp_fund_flow" + qr.where + " ORDER BY id DESC";
        var p = Db.paginate(page, pageSize, select, from, qr.params.toArray());
        List<Record> list = p.getList();
        for (Record r : list) {
            r.set("user", UserCache.getBasic(r.getLong("user_id")));
            String key = getGroupByIdKey();
            Long orgId = r.getLong(key);
            if (null != orgId) {
                Record showInfo = UserCache.getShowInfo(orgId);
                r.set("orgInfo", showInfo);
            }
        }
        renderJson(JsonResult.successWithData(
            new PageResult<>(list, p.getTotalRow(), pageSize, page)
        ));
    }


    public void fundStatByUser() {

        int page = getInt("currentPage", 1);
        int pageSize = getInt("pageSize", 20);

        Long userId = getParaToLong("user_id", null);
        String startTime = getPara("start_time");
        String endTime = getPara("end_time");

        StringBuilder where = new StringBuilder(" WHERE 1=1 ");
        List<Object> params = new ArrayList<>();
        if (userId != null) {
            where.append(" AND user_id = ?");
            params.add(userId);
        }
        if (StringUtil.isNotEmpty(startTime)) {
            where.append(" AND create_time >= ?");
            params.add(startTime);
        }
        if (StringUtil.isNotEmpty(endTime)) {
            where.append(" AND create_time <= ?");
            params.add(endTime);
        }

        String filterField = getFilterField();
        Long filterUserId = getRequestUser().getLong("user_id");
        if (filterField != null && filterUserId != null) {
            where.append(" AND ").append(filterField).append(" = ?");
            params.add(filterUserId);
        }

        String roleKey = getPara("roleKey");
        Long roleUserId = getParaToLong("roleUserId");
        if (StringUtil.isNotEmpty(roleKey) && null != roleUserId) {
            where.append(" AND ").append(roleKey).append(" = ?");
            params.add(roleUserId);
        }


        String select = "SELECT user_id, " +
                "MIN(create_time) AS start_time, " +
                "MAX(create_time) AS end_time, " +
                "sum(change_amount) AS change_amount, " +
                "SUM(CASE WHEN change_type = 1 THEN change_amount ELSE 0 END) AS recharge_amount, " +
                "SUM(CASE WHEN change_type = 2 THEN change_amount ELSE 0 END) AS withdraw_amount, " +
                "SUM(CASE WHEN change_type = 3 THEN change_amount ELSE 0 END) AS open_service_fee, " +
                "SUM(CASE WHEN change_type = 4 THEN change_amount ELSE 0 END) AS close_service_fee, " +
                "SUM(CASE WHEN change_type = 5 THEN change_amount ELSE 0 END) AS close_amount, " +
                "SUM(CASE WHEN change_type = 6 THEN change_amount ELSE 0 END) AS transfer_amount, " +
                "SUM(CASE WHEN change_type = 7 THEN change_amount ELSE 0 END) AS income_amount, " +
                "SUM(CASE WHEN change_type = 8 THEN change_amount ELSE 0 END) AS comepensate_amount, " +
                "SUM(CASE WHEN change_type = 9 THEN change_amount ELSE 0 END) AS commission_amount, " +
                "SUM(CASE WHEN change_type = 10 THEN change_amount ELSE 0 END) AS in_syb_amount, " +
                "SUM(CASE WHEN change_type = 11 THEN change_amount ELSE 0 END) AS out_syb_amount, " +
                "SUM(CASE WHEN change_type = 12 THEN change_amount ELSE 0 END) AS ems_ops_amount, " +
                "SUM(CASE WHEN change_type = 13 THEN change_amount ELSE 0 END) AS ems_ops2_amount ";

        String from = "FROM dctp_fund_flow " + where +
                " GROUP BY user_id ORDER BY start_time DESC";

        var pageData = Db.paginate(page, pageSize, select, from, params.toArray());

        if (CollectUtil.isNotEmpty(pageData.getList())) {
            for (Record r : pageData.getList()) {
                Long uid = r.getLong("user_id");
                Record user = UserCache.getBasic(uid);
                r.set("start_time", DateUtil.format(r.getLocalDateTime("start_time"),"MM-dd HH:mm"));
                r.set("end_time", DateUtil.format(r.getLocalDateTime("end_time"),"MM-dd HH:mm"));

                BigDecimal withdraw_amount =  r.getBigDecimal("withdraw_amount");
                BigDecimal recharge_amount = r.getBigDecimal("recharge_amount");
                BigDecimal ems_ops_amount = r.getBigDecimal("ems_ops_amount");

                BigDecimal addAmount = recharge_amount.add(ems_ops_amount);
                if (addAmount.compareTo(BigDecimal.ZERO) > 0) {
                    BigDecimal rwRatio = withdraw_amount.negate().divide(addAmount,4, RoundingMode.HALF_UP).multiply(BigDecimal.valueOf(100));
                    r.set("rwRatio", rwRatio);
                }else {
                    r.set("rwRatio", 0);
                }

                if (null != user) {
                    r.set("user",UserCache.getShowInfo(uid));
                    String key = getGroupByIdKey();
                    Long orgId = user.getLong(key);
                    if (null != orgId) {
                        Record showInfo = UserCache.getShowInfo(orgId);
                        r.set("orgInfo", showInfo);
                    }
                }
            }
        }
        renderJson(JsonResult.successWithData(
                new PageResult<>(pageData.getList(), pageData.getTotalRow(), pageSize, page)
        ));
    }


    /**
     * GET /fundFlow/export
     * 导出资金流水Excel，支持user_id、change_type、create_time范围
     */
    public void export() {
        int pageSize = 10000; // 导出最大行数
        QueryResult qr = buildFundFlowQuery();
        String sql = "SELECT * FROM dctp_fund_flow" + qr.where + " ORDER BY id DESC LIMIT ?";
        qr.params.add(pageSize);
        List<Record> list = Db.find(sql, qr.params.toArray());
        for (Record r : list) {
            Long uid = r.getLong("user_id");
            if (uid != null) {
                Record user = UserCache.getBasic(uid);
                r.set("user", user != null ? user.getColumns() : null);
            }
            LocalDateTime createTime = r.getLocalDateTime("create_time");
            if (createTime != null) {
                r.set("create_time", DateUtil.format(createTime));
            }
        }
        try (Workbook wb = new XSSFWorkbook()) {
            Sheet sheet = wb.createSheet("资金流水");
            // 表头
            String[] fields = {"id", "user_id", "change_type", "amount", "balance", "create_time"};
            String[] titles = {"ID", "用户ID", "变动类型", "变动金额", "余额", "创建时间"};
            Row head = sheet.createRow(0);
            for (int i = 0; i < titles.length; i++) {
                head.createCell(i).setCellValue(titles[i]);
            }
            // 数据
            int rowIdx = 1;
            for (Record r : list) {
                Row row = sheet.createRow(rowIdx++);
                for (int i = 0; i < fields.length; i++) {
                    Object val = r.get(fields[i]);
                    if (val == null) {
                        row.createCell(i).setCellValue("");
                    } else if (val instanceof Number) {
                        row.createCell(i).setCellValue(((Number) val).toString());
                    } else {
                        row.createCell(i).setCellValue(val.toString());
                    }
                }
            }
            // 响应下载
            HttpServletResponse resp = getResponse();
            String fileName = "fund_flow_" + DateUtil.format(DateUtil.now(), "yyyyMMddHHmmss") + ".xlsx";
            resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            resp.setHeader("Content-Disposition", "attachment; filename=" + fileName);
            wb.write(resp.getOutputStream());
            renderNull();
        } catch (Exception e) {
            renderJson(JsonResult.failWithMessage("导出失败: " + e.getMessage()));
        }
    }


    private QueryResult buildFundFlowQuery() {
        Long userId = getParaToLong("user_id");
        Integer changeType = getParaToInt("change_type", null);
        String startTime = getPara("start_time");
        String endTime = getPara("end_time");
        StringBuilder where = new StringBuilder(" WHERE 1=1 ");
        List<Object> params = new ArrayList<>();
        if (userId != null) {
            where.append(" AND user_id = ?");
            params.add(userId);
        }
        if (changeType != null) {
            where.append(" AND change_type = ?");
            params.add(changeType);
        }
        if (startTime != null && !startTime.isEmpty()) {
            where.append(" AND create_time >= ?");
            params.add(startTime);
        }
        if (endTime != null && !endTime.isEmpty()) {
            where.append(" AND create_time <= ?");
            params.add(endTime);
        }
        String filterField = getFilterField();
        if (filterField != null) {
            where.append(" AND ").append(filterField).append(" = ?");
            params.add(getRequestUser().getLong("user_id"));
        }

        String roleKey = getPara("roleKey");
        Long roleUserId = getParaToLong("roleUserId");
        if (StringUtil.isNotEmpty(roleKey) && null != roleUserId) {
            where.append(" AND ").append(roleKey).append(" = ?");
            params.add(roleUserId);
        }

        return new QueryResult(where, params);
    }
}
